﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.OleDb;
using System.Data;
using System.Windows.Forms;
using System.Diagnostics;
using BaseManage.log;

namespace BaseManage.db
{
    public class dbOperate
    {
        OleDbConnection DBConnection;
        LogWrite log = new LogWrite();
        Boolean debug = true;

        /// <summary>
        /// 打开本地数据库连接
        /// </summary>
        /// <returns></returns>
        public bool OpenConnect()
        {
            string root =  Application.StartupPath;
            string constr = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + root + "\\db\\jidiguanli.mdb";//数据库连接字
            DBConnection = new OleDbConnection(constr);
            try
            {
                DBConnection.Open();
                return true;
            }
            catch (Exception ex)
            {
                log.WriteFile("dbOperate类的OpenConnect方法异常：" + ex.Message);
                return false;
            }
        }

        /// <summary>
        /// 关闭本地数据库连接
        /// </summary>
        /// <returns></returns>
        public bool CloseConnect()
        {
            try
            {
                DBConnection.Close();
                return true;
            }
            catch (Exception ex)
            {
                log.WriteFile("dbOperate类的CloseConnect方法异常：" + ex.Message);
                return false;
            }
        }

        /// <summary>
        /// 获取一定条件下数据数量
        /// </summary>
        /// <param name="tablename">数据表名称</param>
        /// <param name="condition">条件</param>
        /// <returns></returns>
        public int GetCount(string tablename,string condition)
        {
            if (OpenConnect())
            {
                try
                {
                    OleDbCommand com = new OleDbCommand("select count(*) from " + tablename + " " + condition, DBConnection);
                    int count = Convert.ToInt32(com.ExecuteScalar());
                    CloseConnect();
                    return count;
                }
                catch (Exception ex)
                {
                    log.WriteFile("dbOperate类的GetCount方法异常：" + ex.Message);
                    CloseConnect();
                    return 0;
                }
            }
            else
            {
                MessageBox.Show("数据库打开失败");
                if (debug)
                    MessageBox.Show("failed in getcount;db path:"+this.DBConnection.ConnectionString);
                return 0;
            }
        }

        /// <summary>
        /// 获取某表数据总量
        /// </summary>
        /// <param name="tablename"></param>
        /// <returns></returns>
        public int GetCount(string tablename)
        {
            return GetCount(tablename, "");
        }

        /// <summary>
        /// 获取查询数据
        /// </summary>
        /// <returns></returns>
        public DataTable GetDate(string sql)
        {
            if (OpenConnect())
            {
                try
                {
                    OleDbDataAdapter adapter = new OleDbDataAdapter(sql, DBConnection);
                    DataTable table = new DataTable();
                    adapter.Fill(table);
                    CloseConnect();
                    return table;
                }
                catch (Exception ex)
                {
                    log.WriteFile("dbOperate类的GetDate方法异常：" + ex.Message);
                    CloseConnect();
                    return null;
                }
            }
            else
            {
                MessageBox.Show("数据库打开失败");
                if (debug)
                    MessageBox.Show("failed in getdata;db path:" + this.DBConnection.ConnectionString);
                
                return null;
            }

        }

        /// <summary>
        /// 获取查询数据
        /// </summary>
        /// <returns></returns>
        public DataSet GetDataSet(string sql)
        {
            if (OpenConnect())
            {
                try
                {
                    OleDbDataAdapter adapter = new OleDbDataAdapter(sql, DBConnection);
                    DataSet dataset = new DataSet();
                    adapter.Fill(dataset, "data");
                    CloseConnect();
                    return dataset;
                }
                catch (Exception ex)
                {
                    log.WriteFile("dbOperate类的GetDateset方法异常：" + ex.Message);
                    CloseConnect();
                    return null;
                }
            }
            else
            {
                MessageBox.Show("数据库打开失败");
                if (debug)
                    MessageBox.Show("failed in getdataset;db path:" + this.DBConnection.ConnectionString);
                
                return null;
            }
        }

        /// <summary>
        /// 数据更新和插入
        /// </summary>
        /// <param name="sql"></param>
        /// <returns></returns>
        public bool UpdateAndInsert(string sql)
        {
            if (OpenConnect())
            {
                try
                {
                    OleDbCommand com = new OleDbCommand(sql, DBConnection);
                    com.ExecuteNonQuery();
                    CloseConnect();
                    return true;
                }
                catch (Exception ex)
                {
                    log.WriteFile("dbOperate类的UpdateAndInsert方法异常：" + ex.Message);
                    CloseConnect();
                    return false;
                }
            }
            else
            {
                MessageBox.Show("数据库打开失败");
                if (debug)
                    MessageBox.Show("failed in UpdateAndInsert;db path:" + this.DBConnection.ConnectionString);
                
                return false;
            }

        }

        /// <summary>
        /// 数据删除
        /// </summary>
        /// <param name="sql"></param>
        /// <returns></returns>
        public bool Delete(string sql)
        {
            if (OpenConnect())
            {
                try
                {
                    OleDbCommand com = new OleDbCommand(sql, DBConnection);
                    com.ExecuteNonQuery();
                    CloseConnect();
                    return true;
                }
                catch (Exception ex)
                {
                    log.WriteFile("dbOperate类的Delete方法异常：" + ex.Message);
                    CloseConnect();
                    return false;
                }
            }
            else
            {
                MessageBox.Show("数据库打开失败");
                if (debug)
                    MessageBox.Show("failed in Delete;db path:" + this.DBConnection.ConnectionString);
                
                return false;
            }

        }

        /// <summary>
        /// 获取第一行第一列数据
        /// </summary>
        /// <param name="sql"></param>
        /// <returns></returns>
        public string  GetOneData(string sql)
        {
            if (OpenConnect())
            {
                try
                {
                    OleDbCommand com = new OleDbCommand(sql, DBConnection);
                    string str = com.ExecuteScalar().ToString();
                    CloseConnect();
                    return str;
                }
                catch (Exception ex)
                {
                    log.WriteFile("dbOperate类的GetOneData方法异常：" + ex.Message);
                    CloseConnect();
                    return "";
                }
            }
            else
            {
                MessageBox.Show("数据库打开失败");
                if (debug)
                    MessageBox.Show("failed in GetOneData;db path:" + this.DBConnection.ConnectionString);
                
                return "";
            }

        }
    }
}
